package course.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import course.dao.IDeanDao;
import course.db.DbConnection;
import course.po.Dean;
import course.view.DeanJPanel;

public class DeanDaoImpl implements IDeanDao {
	private DeanJPanel deanJPanel;

	/**
	 *
	 * 新增教务员信息
	 *
	 */
	@Override
	public void save(Dean dean) {
		Connection conn = DbConnection.getConnection();
		int deaId = dean.getDeaId();
		String deaNo = dean.getDeaNo();
		String deaName = dean.getDeaName();
		String deaSex = dean.getDeaSex();
		Date deaAge = dean.getDeaAge();
		String sql = "INSERT INTO dean VALUES(?,?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, deaId);
			ps.setString(2, deaNo);
			ps.setString(3, deaName);
			ps.setString(4, deaSex);
			ps.setDate(5, (java.sql.Date) deaAge);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}
	}

	/**
	 *
	 * 更新教师信息
	 *
	 */
	@Override
	public void update(Dean dean) {
		Connection conn = DbConnection.getConnection();
		int deaId = dean.getDeaId();
		String deaNo = dean.getDeaNo();
		String deaName = dean.getDeaName();
		String deaSex = dean.getDeaSex();
		Date deaAge = dean.getDeaAge();
		String sql = "UPDATE dean SET dean_no=?,dean_name=?,dean_sex=?,dean_age=?" + " WHERE dean_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, deaNo);
			ps.setString(2, deaName);
			ps.setString(3, deaSex);
			ps.setDate(4, (java.sql.Date) deaAge);
			ps.setInt(5, deaId);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 *
	 * 删除教务员信息
	 */
	@Override
	public void delete(int id) {
		Connection conn = DbConnection.getConnection();
		String sql = "DELETE FROM dean WHERE dean_id=?";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(null, ps);
		}

	}

	/**
	 * 根据编号查询教务员的信息 dean_id为主键
	 *
	 */
	@Override
	public Dean get(int id) {
		Connection conn = DbConnection.getConnection();
		List<Dean> deanList = new ArrayList<Dean>();
		String sql = "SELECT dean_id,dean_no,dean_name,dean_sex,dean_age FROM dean WHERE dean_id=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				int deaId = rs.getInt(1);
				String deaNo = rs.getString(2);
				String deaName = rs.getString(3);
				String deaSex = rs.getString(4);
				Date deaAge = rs.getDate(5);
				Dean dean = new Dean(deaId, deaNo, deaName, deaSex, deaAge);
				deanList.add(dean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return deanList.get(0);
	}

	/**
	 * 根据姓名dean_name查询教务员的信息
	 *
	 */
	@Override
	public Dean getOne(String name) {
		Connection conn = DbConnection.getConnection();
		List<Dean> deanList = new ArrayList<Dean>();
		String sql = "SELECT dean_id,dean_no,dean_name,dean_sex,dean_age FROM dean WHERE dean_name =?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, name);
			rs = ps.executeQuery();
			while (rs.next()) {
				int deaId = rs.getInt(1);
				String deaNo = rs.getString(2);
				String deaName = rs.getString(3);
				String deaSex = rs.getString(4);
				Date deaAge = rs.getDate(5);
				Dean dean = new Dean(deaId, deaNo, deaName, deaSex, deaAge);
				deanList.add(dean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return deanList.get(0);
	}
<<<<<<< HEAD
	/**
	 * 根据教职工号dean_no查询教务员的信息
	 *
	 */
	@Override
	public Dean getDean(String dno) {
		Connection conn = DbConnection.getConnection();
		List<Dean> deanList = new ArrayList<Dean>();
		String sql = "SELECT dean_no,dean_name,dean_sex,dean_age FROM dean WHERE dean_no=?";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dno);
			rs = ps.executeQuery();
			while (rs.next()) {
				String deaNo = rs.getString(1);
				String deaName = rs.getString(2);
				String deaSex = rs.getString(3);
				Date deaAge = rs.getDate(4);
				Dean dean = new Dean( deaNo, deaName, deaSex, deaAge);
				deanList.add(dean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return deanList.get(0);
	}
=======

>>>>>>> refs/remotes/origin/master
	/**
	 *
	 * 查询所有教务员的信息
	 */
	@Override
	public List<Dean> findAll() {
		Connection conn = DbConnection.getConnection();
		List<Dean> deanList = new ArrayList<Dean>();
		String sql = "SELECT dean_id,dean_no,dean_name,dean_sex,dean_age" + " FROM dean";
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				int deaId = rs.getInt(1);
				String deaNo = rs.getString(2);
				String deaName = rs.getString(3);
				String deaSex = rs.getString(4);
				Date deaAge = rs.getDate(5);
				Dean dean = new Dean(deaId, deaNo, deaName, deaSex, deaAge);
				deanList.add(dean);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DbConnection.close(rs, ps);
		}
		return deanList;
	}
}
